/*^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^*/
--Create Database ETLMetaData
/*^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^*/
USE master
IF  NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'ETLMetaData')
	CREATE DATABASE ETLMetaData
GO

/*^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^*/
--Create Table DFLMetaData
/*^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^*/
USE ETLMetaData
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DFLMetaData]') AND type in (N'U'))
	DROP TABLE [dbo].[DFLMetaData]
GO
CREATE TABLE DFLMetaData
(
PackageID VARCHAR(38) NOT NULL,
PackageName VARCHAR(100) NOT NULL,
DataflowID VARCHAR(38) NOT NULL,
DataflowName VARCHAR(200) NOT NULL,
IOType CHAR(1) NULL,
IOName VARCHAR(200) NULL,
IOID INT NULL,
ColumnName VARCHAR(200) NULL,
ColumnDesc	VARCHAR(500) NULL,
LineageID INT NOT NULL,
ColumnID INT NOT NULL,
ColumnDataType VARCHAR(30) NULL,
ColumnPrecision	INT	NULL,
ColumnScale INT NULL,
UpstreamComponentName VARCHAR(200) NULL,
SortKeyPosition	INT NULL,
ColumnCodePage INT NULL,
MappedColumnID INT NULL,
UsageType VARCHAR(50) NULL,
ColumnType VARCHAR(50) NULL,
CreatedBy  AS SYSTEM_USER,
CreatedTime AS GETDATE(),

CONSTRAINT PK_DFLMetaData PRIMARY KEY CLUSTERED (PackageID,DataflowID,ColumnID)
)
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DFLMetaData]') AND name = N'IDX_Names')
DROP INDEX [IDX_Names] ON [dbo].[DFLMetaData] WITH ( ONLINE = OFF )
GO
CREATE NONCLUSTERED INDEX [IDX_Name] ON [dbo].[DFLMetaData]
(
	[PackageName] ASC,[DataflowName] ASC,[ColumnID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
	

